package bam.dao.impl;



import java.util.Date;
import java.util.List;

import org.hibernate.Query;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;


import bam.dao.DealrecordDao;
import bam.entity.Dateil;
import bam.entity.DateilId;
import bam.entity.Realtimecombo;
import bam.entity.RealtimecomboId;
import bam.entity.Realtimevip;
import bam.entity.Recordcombo;
import bam.entity.RecordcomboId;
import bam.entity.Recordvip;
import bam.util.PagingUtil;

public class DealrecordDaoImpl extends HibernateDaoSupport implements
		DealrecordDao {

	public void selRealTimeVip(PagingUtil<Realtimevip> pu, Realtimevip timeVip)
			throws Exception {
		String areaidstr = "";
		if(timeVip.getId().getAreaid()!=-1){
			areaidstr = timeVip.getId().getAreaid()+"";
		}
	
		pu.setLogCount(this.scountRealTimeVip(timeVip, areaidstr));
		String sql = " select * FROM realtimevip WHERE " +
						"set_time > ? "+
						"and areaid LIKE ? " +
						"and account LIKE ? " +
						"and pledge > ?";
		Query query = this.getSession().createSQLQuery(sql).addEntity(Realtimevip.class);
		query.setParameter(0, timeVip.getId().getSetTime());
		query.setParameter(1, "%"+areaidstr+"%");
		query.setParameter(2, "%"+timeVip.getId().getAccount()+"%");
		query.setParameter(3, timeVip.getId().getPledge());
		int start=(pu.getPageNo()-1)*pu.getPageSize();
		int size = pu.getPageSize();
		query.setFirstResult(start);
		query.setMaxResults(size);
		List <Realtimevip> list = query.list();
		pu.setList(list);
	}
	private int scountRealTimeVip(Realtimevip timeVip,String areaidstr){
		String sql = " select count(id) FROM realtimevip WHERE " +
				"set_time > ? "+
				"and areaid LIKE ? " +
				"and account LIKE ? " +
				"and pledge > ?";
		Query query = this.getSession().createSQLQuery(sql);
		query.setParameter(0, timeVip.getId().getSetTime());
		query.setParameter(1, "%"+areaidstr+"%");
		query.setParameter(2, "%"+timeVip.getId().getAccount()+"%");
		query.setParameter(3, timeVip.getId().getPledge());
		String uniqueResult = query.uniqueResult().toString();
		System.out.println(uniqueResult);
		return Integer.parseInt(uniqueResult);
	}
	
	
	public void selRealTimeCombo(PagingUtil<Realtimecombo> pu,
			Realtimecombo realtimecombo) throws Exception {
		RealtimecomboId realCom = realtimecombo.getId();
		String areaidstr = "";
		String status = "";
		String combo_unit ="";
		
		if(realCom.getStatus()!=-1){
			status = realCom.getStatus()+"";
		}
		if(realCom.getComboUnit()!=-1){
			combo_unit = realCom.getComboUnit()+"";
		}
		if(realCom.getAreaId()!=-1){
			areaidstr = realCom.getAreaId()+"";
		}
		
		pu.setLogCount(scountRealTimeCombo(realCom, status, combo_unit, areaidstr));
		String sql= "SELECT * FROM realtimecombo WHERE " +
				"buy_time > ? " +
				"AND `end_time` > ? " +
				"AND `status` LIKE ? " +
				"AND `money` > ? " +
				"AND `combo_unit` LIKE ? " +
				"AND `area_id` LIKE ? " +
				"AND `time_long` > ? " +
				"AND (`account`LIKE ? " +
				"OR `combo_name` LIKE ?)"	;
		Query query = this.getSession().createSQLQuery(sql).addEntity(Realtimecombo.class);
		query.setParameter(0, realCom.getBuyTime());
		query.setParameter(1, realCom.getEndTime());
		query.setParameter(2, "%"+status+"%");
		query.setParameter(3, realCom.getMoney());
		query.setParameter(4, "%"+combo_unit+"%");
		query.setParameter(5, "%"+areaidstr+"%");
		query.setParameter(6, realCom.getTimeLong());
		query.setParameter(7,  "%"+realCom.getAccount()+"%");
		query.setParameter(8,  "%"+realCom.getComboName()+"%");
		
		int start=(pu.getPageNo()-1)*pu.getPageSize();
		int size = pu.getPageSize();
		query.setFirstResult(start);
		query.setMaxResults(size);
		List <Realtimecombo> list = query.list();
		pu.setList(list);
	}
	private int scountRealTimeCombo(RealtimecomboId realCom,String status,String combo_unit,
											String areaidstr){
		String sql= "SELECT count(account) FROM realtimecombo WHERE " +
				"buy_time > ? " +
				"AND `end_time` > ? " +
				"AND `status` LIKE ? " +
				"AND `money` > ? " +
				"AND `combo_unit` LIKE ? " +
				"AND `area_id` LIKE ? " +
				"AND `time_long` > ? " +
				"AND (`account`LIKE ? " +
				"OR `combo_name` LIKE ?)"	;
		Query query = this.getSession().createSQLQuery(sql);
		query.setParameter(0, realCom.getBuyTime());
		query.setParameter(1, realCom.getEndTime());
		query.setParameter(2, "%"+status+"%");
		query.setParameter(3, realCom.getMoney());
		query.setParameter(4, "%"+combo_unit+"%");
		query.setParameter(5, "%"+areaidstr+"%");
		query.setParameter(6, realCom.getTimeLong());
		query.setParameter(7,  "%"+realCom.getAccount()+"%");
		query.setParameter(8,  "%"+realCom.getComboName()+"%");
		String uniqueResult = query.uniqueResult().toString();
		System.out.println(uniqueResult);
		return Integer.parseInt(uniqueResult);
	}
	
	
	public void selRecordVip(PagingUtil<Recordvip> pu, Recordvip recordvip)
			throws Exception {
		String areaidstr = "";
		if(recordvip.getId().getAreaid()!=-1){
			areaidstr = recordvip.getId().getAreaid()+"";
		}
	
		pu.setLogCount(this.scountRecordVip(recordvip, areaidstr));
		String sql = " select * FROM recordvip WHERE " +
						"set_time > ? "+
						"and areaid LIKE ? " +
						"and account LIKE ? " +
						"and motion LIKE ? " +
						"and pledge > ?";
				
		Query query = this.getSession().createSQLQuery(sql).addEntity(Recordvip.class);
		query.setParameter(0, recordvip.getId().getSetTime());
		query.setParameter(1, "%"+areaidstr+"%");
		query.setParameter(2, "%"+recordvip.getId().getAccount()+"%");
		query.setParameter(3, "%"+recordvip.getId().getMotion()+"%");
		query.setParameter(4, recordvip.getId().getPledge());
		
		int start=(pu.getPageNo()-1)*pu.getPageSize();
		int size = pu.getPageSize();
		query.setFirstResult(start);
		query.setMaxResults(size);
		List <Recordvip> list = query.list();
		pu.setList(list);
		
	}
	
	public int scountRecordVip(Recordvip recordvip,String areaidstr){
		String sql = " select count(id) FROM recordvip WHERE " +
				"set_time > ? "+
				"and areaid LIKE ?" +
				"and account LIKE ?" +
				"and motion LIKE ? " +
				"and pledge > ?";
		Query query = this.getSession().createSQLQuery(sql);
		query.setParameter(0, recordvip.getId().getSetTime());
		query.setParameter(1, "%"+areaidstr+"%");
		query.setParameter(2, "%"+recordvip.getId().getAccount()+"%");      
		
		query.setParameter(3, "%"+recordvip.getId().getMotion()+"%");
		query.setParameter(4, recordvip.getId().getPledge());
		String uniqueResult = query.uniqueResult().toString();
		System.out.println(uniqueResult);
		return Integer.parseInt(uniqueResult);
	}
	
	public void selRecordCombo(PagingUtil<Recordcombo> pu,
			Recordcombo recordcombo) throws Exception {
		RecordcomboId combo = recordcombo.getId();
		String areaidstr = "";
		String combo_unit ="";
		
	
		if(combo.getComboUnit()!=-1){
			combo_unit = combo.getComboUnit()+"";
		}
		if(combo.getAreaId()!=-1){
			areaidstr = combo.getAreaId()+"";
		}
		
		pu.setLogCount(scountRecordCombo(combo,  combo_unit, areaidstr));
		String sql= "SELECT * FROM recordcombo WHERE " +
				"buy_time > ? " +
				"AND `end_time` > ? " +
				"AND `money` > ? " +
				"AND `combo_unit` LIKE ? " +
				"AND `area_id` LIKE ? " +
				"AND `time_long` > ? " +
				"AND (`account`LIKE ? " +
				"OR `combo_name` LIKE ?)"	;
		Query query = this.getSession().createSQLQuery(sql).addEntity(Recordcombo.class);
		query.setParameter(0, combo.getBuyTime());
		query.setParameter(1, combo.getEndTime());
		query.setParameter(2, combo.getMoney());
		query.setParameter(3, "%"+combo_unit+"%");
		query.setParameter(4, "%"+areaidstr+"%");
		query.setParameter(5, combo.getTimeLong());
		query.setParameter(6,  "%"+combo.getAccount()+"%");
		query.setParameter(7,  "%"+combo.getComboName()+"%");
		
		int start=(pu.getPageNo()-1)*pu.getPageSize();
		int size = pu.getPageSize();
		query.setFirstResult(start);
		query.setMaxResults(size);
		List <Recordcombo> list = query.list();
		pu.setList(list);
		
	}
	public int scountRecordCombo(RecordcomboId combo,String areaidstr,String combo_unit){
		
		String sql= "SELECT count(account) FROM recordcombo WHERE " +
				"buy_time > ? " +
				"AND `end_time` > ? " +
				"AND `money` > ? " +
				"AND `combo_unit` LIKE ? " +
				"AND `area_id` LIKE ? " +
				"AND `time_long` > ? " +
				"AND (`account`LIKE ? " +
				"OR `combo_name` LIKE ?)"	;
		Query query = this.getSession().createSQLQuery(sql);
		query.setParameter(0, combo.getBuyTime());
		query.setParameter(1, combo.getEndTime());
		query.setParameter(2, combo.getMoney());
		query.setParameter(3, "%"+combo_unit+"%");
		query.setParameter(4, "%"+areaidstr+"%");
		query.setParameter(5, combo.getTimeLong());
		query.setParameter(6,  "%"+combo.getAccount()+"%");
		query.setParameter(7,  "%"+combo.getComboName()+"%");
		String uniqueResult = query.uniqueResult().toString();
		System.out.println(uniqueResult);
		return Integer.parseInt(uniqueResult);
	}
	
//////	
	
	public void selRecordDetail(PagingUtil<Dateil> pu,String userid, String suosou,
			String areaID, Date endTime ,Date startTime)
			throws Exception {
		
		String type=suosou;
		String useType = suosou;
		String account = suosou;
		if(suosou.length()<1){
			account = "isEmpty";
		}
		pu.setLogCount(this.scountDetail(userid,type, useType, areaID, account, endTime, startTime));
		String sql= "SELECT * FROM dateil WHERE dateil.`spend_time`> ? " +
				"AND dateil.`spend_time` < ? " +
				"AND (dateil.`type` LIKE ? " +
				"OR dateil.`use_type` LIKE ? " +
				"OR dateil.`account` LIKE ? )" +
				"AND dateil.`areaid` LIKE ? " +
				"AND dateil.`userid` LIKE ? ";
		Query query = this.getSession().createSQLQuery(sql).addEntity(Dateil.class);
		query.setParameter(0, startTime);
		query.setParameter(1, endTime);
		query.setParameter(2, "%"+type+"%");
		query.setParameter(3, "%"+useType+"%");
		query.setParameter(4, "%"+account+"%");
		query.setParameter(5, "%"+areaID+"%");
		query.setParameter(6, "%"+userid+"%");
		
		int start=(pu.getPageNo()-1)*pu.getPageSize();
		int size = pu.getPageSize();
		query.setFirstResult(start);
		query.setMaxResults(size);
		List <Dateil> list = query.list();
		pu.setList(list);
	}
	
	public int scountDetail(String userid,String type,String useType,
			String areaID,String account, Date endTime ,Date startTime){
		String sql= "SELECT count(id) FROM dateil WHERE dateil.`spend_time`> ? " +
				"AND dateil.`spend_time` < ? " +
				"AND (dateil.`type` LIKE ? " +
				"OR dateil.`use_type` LIKE ? " +
				"OR dateil.`account` LIKE ? )" +
				"AND dateil.`areaid` LIKE ? " +
				"AND dateil.`userid` LIKE ? ";
		Query query = this.getSession().createSQLQuery(sql);
		query.setParameter(0, startTime);
		query.setParameter(1, endTime);
		query.setParameter(2, "%"+type+"%");
		query.setParameter(3, "%"+useType+"%");
		query.setParameter(4, "%"+account+"%");
		query.setParameter(5, "%"+areaID+"%");
		query.setParameter(6, "%"+userid+"%");
		String uniqueResult = query.uniqueResult().toString();
		
		System.out.println("uniqueResult :"+uniqueResult);
		return Integer.parseInt(uniqueResult);
		
	}
}
